﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;

namespace AnalyseCrmMana
{
    public class TradingChannelsMana
    {
        private string _countmm = "";
        private string _guestno = "";
        private string _zjcode = "";
        private string _zjtype;
        private string _transactionqd;
        private string _transactiontype;
        private decimal _transactionmenoy;




        string begindate = "";
        string enddate = "";

        /// <summary>
        ///  
        /// </summary>
        public string COUNTMM
        {
            set { _countmm = value; }
            get { return _countmm; }
        }

        /// <summary>
        /// 客户编号
        /// </summary>
        public string GUESTNO
        {
            set { _guestno = value; }
            get { return _guestno; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string ZJCODE
        {
            set { _zjcode = value; }
            get { return _zjcode; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string ZJTYPE
        {
            set { _zjtype = value; }
            get { return _zjtype; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string TRANSACTIONQD
        {
            set { _transactionqd = value; }
            get { return _transactionqd; }
        }

        /// <summary>
        /// 买入/卖出
        /// </summary>
        public string TRANSACTIONTYPE
        {
            set { _transactiontype = value; }
            get { return _transactiontype; }
        }


        /// <summary>
        ///  金额
        /// </summary>
        public decimal TRANSACTIONMENOY
        {
            set { _transactionmenoy = value; }
            get { return _transactionmenoy; }
        }

        /// <summary>
        /// 所有数据。
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<TradingChannelsMana> GetTradingChannelsConditionAll(string strEndDate, string Condition)
        {
            string begindate = DateTime.Parse(strEndDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(strEndDate).ToString("yyyy-MM-dd");

            string strSqlwhere = SetGuestNOsSql(begindate, enddate, Condition);

            string strSql = @"select COUNTMM,GUESTNO,ZJCODE,ZJTYPE,TRANSACTIONQD,TRANSACTIONTYPE,TRANSACTIONMENOY From Tbt_ClientTransactionBehavior 
                       where  " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "' ";

            return GetAllTradingChannels(strSql);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<TradingChannelsMana> GetTradingChannelsCondition(string strEndDate, string Condition, int FromIndex)
        {
            string begindate = DateTime.Parse(strEndDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(strEndDate).ToString("yyyy-MM-dd");

            int fromIndex = FromIndex - 1;
            int toIndex = 7 + FromIndex;

            string strSqlwhere = SetGuestNOsSql(begindate, enddate, Condition);

            string strSql = @"select B.* from (  select ROW_NUMBER() over(order by COUNTMM ) rownumber, COUNTMM,GUESTNO,ZJCODE,ZJTYPE,TRANSACTIONQD,TRANSACTIONTYPE,TRANSACTIONMENOY from Tbt_ClientTransactionBehavior 
            A where 1=1 and " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "'  )B  WHERE  B.rownumber > " + fromIndex + "  and B.rownumber < " + toIndex;

            //            string strSql = @"select COUNTMM,GUESTNO,ZJCODE,ZJTYPE,TRANSACTIONQD,TRANSACTIONTYPE,TRANSACTIONMENOY From Tbt_ClientTransactionBehavior 
            //            where  " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "' ";

            return GetAllTradingChannels(strSql);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int GetTradingChannelsConditionCount(string strEndDate, string Condition)
        {
            string begindate = DateTime.Parse(strEndDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(strEndDate).ToString("yyyy-MM-dd");


            string strSqlwhere = SetGuestNOsSql(begindate, enddate, Condition);


            string strSql = @"select count(*) From Tbt_ClientTransactionBehavior 
                        where  " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "' ";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                return int.Parse(dtChannel.Rows[0][0].ToString());
            }
            else
            {
                return 0;
            }

        }

        /// <summary>
        /// 获取对象数据。
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public IList<TradingChannelsMana> GetAllTradingChannels(string strSql)
        {
            ExecuteSql sqldbAccess = new ExecuteSql();
            DataSet ds = sqldbAccess.ExecuteDataset(strSql);
            IList<TradingChannelsMana> traChnnList = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                traChnnList = new List<TradingChannelsMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    TradingChannelsMana tradChnnel = new TradingChannelsMana();
                    tradChnnel._countmm = dr["countmm"].ToString();
                    tradChnnel._guestno = dr["guestno"].ToString();
                    tradChnnel._transactionmenoy = decimal.Parse(dr["transactionmenoy"].ToString());
                    tradChnnel._transactionqd = dr["transactionqd"].ToString();
                    tradChnnel._transactiontype = dr["transactiontype"].ToString();
                    tradChnnel._zjcode = dr["zjcode"].ToString();
                    tradChnnel._zjtype = dr["zjtype"].ToString();
                    traChnnList.Add(tradChnnel);
                }
            }
            return traChnnList;
        }

        /// <summary>
        /// 各交易渠道交易次数统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories001(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            if (strType == "0")
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-1).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }
            else
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-6).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }

            string strSql = @"select TRANSACTIONQD as JYChnnel, count(*) as JYCount from Tbt_ClientTransactionBehavior
                where  COUNTMM between  '" + begindate + "' and '" + enddate + "' group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYCount"] != DBNull.Value)
                    {
                        strData += dr["JYCount"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 各交易渠道交易金额统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories002(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            if (strType == "0")
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-1).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }
            else
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-6).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }

            string strSql = @"select TRANSACTIONQD as JYChnnel, sum(TRANSACTIONMENOY) as JYCurrency from Tbt_ClientTransactionBehavior 
                where COUNTMM between  '" + begindate + "' and '" + enddate + "' group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYCurrency"] != DBNull.Value)
                    {
                        strData += dr["JYCurrency"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 各交易渠道交易人数
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories003(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            if (strType == "0")
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-1).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }
            else
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-6).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }

            string strSql = @"select TRANSACTIONQD as JYChnnel, count(distinct TRANSACTIONDT) as JYPeopCount from Tbt_ClientTransactionBehavior 
                where COUNTMM between  '" + begindate + "' and '" + enddate + "' group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYPeopCount"] != DBNull.Value)
                    {
                        strData += dr["JYPeopCount"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 各交易渠道交易佣金金额
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories004(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            if (strType == "0")
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-1).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }
            else
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-6).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }

            string strSql = @"select TRANSACTIONQD as JYChnnel, sum(BUYINGCOMM) as YJMoney from Tbt_ClientTransactionBehavior 
                where COUNTMM between  '" + begindate + "' and '" + enddate + "' group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["YJMoney"] != DBNull.Value)
                    {
                        strData += dr["YJMoney"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 各交易渠道人均佣金贡献额统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories005(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            if (strType == "0")
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-1).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }
            else
            {
                begindate = DateTime.Parse(LastDate).AddMonths(-6).ToString("yyyy-MM-dd");
                enddate = LastDate;
            }

            string strSql = @"select TRANSACTIONQD as JYChnnel, sum(BUYINGCOMM)/COUNT(distinct GUESTNO) as YJMoneyPeop  From Tbt_ClientTransactionBehavior 
                where COUNTMM between  '" + begindate + "' and '" + enddate + "' group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["YJMoneyPeop"] != DBNull.Value)
                    {
                        strData += dr["YJMoneyPeop"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 交易次数异常监测统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories006(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            begindate = DateTime.Parse(LastDate).AddMonths(-3).ToString("yyyy-MM-dd");
            enddate = DateTime.Parse(LastDate).ToString("yyyy-MM-dd");


            string strChannel = "";
            switch (strType)
            {
                case "0":
                    strChannel = "通信达";
                    break;
                case "1":
                    strChannel = "柜台委托";
                    break;
                case "2":
                    strChannel = "网上交易";
                    break;
                case "3":
                    strChannel = "电话委托";
                    break;
                case "4":
                    strChannel = "程序化交易";
                    break;
                default:
                    break;
            }


            strXAxis = "['最小值','最大值','平均值','中位数']";

            strData = "[";
            strData += GetMinJYCount(begindate, enddate, strChannel) + ",";
            strData += GetMaxJYCount(begindate, enddate, strChannel) + ",";
            strData += GetAVGJYCount(begindate, enddate, strChannel) + ",";
            strData += GetMidJYCount(begindate, enddate, strChannel) + "";
            strData += "]";

            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }

        /// <summary>
        /// 获取最小交易数
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private int GetMinJYCount(string begindate, string endDate, string chnnel)
        {
            string strSql = @"select top 1 GUESTNO, count( GUESTNO)   From Tbt_ClientTransactionBehavior 
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' group by GUESTNO  order by count(GUESTNO);";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][1] != DBNull.Value)
                {
                    return int.Parse(dtChannel.Rows[0][1].ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 获取最大交易数
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private int GetMaxJYCount(string begindate, string endDate, string chnnel)
        {
            int MinCount = 0;
            string strSql = @"select top 1 GUESTNO, count( GUESTNO)   From Tbt_ClientTransactionBehavior 
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' group by GUESTNO  order by count(GUESTNO) desc ";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][1] != DBNull.Value)
                {
                    return int.Parse(dtChannel.Rows[0][1].ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 获取平均交易次数
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private int GetAVGJYCount(string begindate, string endDate, string chnnel)
        {
            try
            {
                string strSql = @"select count(GUESTNO)/count(distinct GUESTNO)   From Tbt_ClientTransactionBehavior 
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' ";
                DataTable dtChannel = GetAllTradingChannelsTable(strSql);
                if (dtChannel.Rows.Count > 0)
                {
                    if (dtChannel.Rows[0][0] != DBNull.Value)
                    {
                        return int.Parse(dtChannel.Rows[0][0].ToString());
                    }
                }
            }
            catch
            {
                return 0;
            }
            return 0;
        }

        /// <summary>
        /// 获取平均交易次数
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private int GetMidJYCount(string begindate, string endDate, string chnnel)
        {
            int MinCount = GetMinJYCount(begindate, endDate, chnnel);
            int MaxCount = GetMaxJYCount(begindate, endDate, chnnel);
            return (MinCount + MaxCount) / 2;
        }


        /// <summary>
        /// 交易金额异常监测统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories007(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            begindate = DateTime.Parse(LastDate).AddMonths(-3).ToString("yyyy-MM-dd");
            enddate = DateTime.Parse(LastDate).ToString("yyyy-MM-dd");

            string strChannel = "";
            switch (strType)
            {
                case "0":
                    strChannel = "通信达";
                    break;
                case "1":
                    strChannel = "柜台委托";
                    break;
                case "2":
                    strChannel = "网上交易";
                    break;
                case "3":
                    strChannel = "电话委托";
                    break;
                case "4":
                    strChannel = "程序化交易";
                    break;
                default:
                    break;
            }


            strXAxis = "['最小值','最大值','平均值','中位数']";

            strData = "[";
            strData += GetMinJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetMaxJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetAvgJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetMidJYMoney(begindate, enddate, strChannel) + "";
            strData += "]";

            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 获取最小交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMinJYMoney(string begindate, string endDate, string chnnel)
        {
            int MinCount = 0;
            string strSql = @"select top 1 GUESTNO, sum(TRANSACTIONMENOY)  From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' group by GUESTNO  order by sum(TRANSACTIONMENOY)  ";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][1] != DBNull.Value)
                {
                    return decimal.Parse(dtChannel.Rows[0][1].ToString());
                }
            }
            return 0;
        }


        /// <summary>
        /// 获取最大交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMaxJYMoney(string begindate, string endDate, string chnnel)
        {
            string strSql = @"select top 1 GUESTNO, sum(TRANSACTIONMENOY)  From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' group by GUESTNO  order by sum(TRANSACTIONMENOY) desc ";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][1] != DBNull.Value)
                {
                    return decimal.Parse(dtChannel.Rows[0][1].ToString());
                }
            }
            return 0;
        }


        /// <summary>
        /// 获取平均交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetAvgJYMoney(string begindate, string endDate, string chnnel)
        {
            try
            {
                string strSql = @"select sum(TRANSACTIONMENOY)/count(distinct GUESTNO)  From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "' ";
                DataTable dtChannel = GetAllTradingChannelsTable(strSql);
                if (dtChannel.Rows.Count > 0)
                {
                    if (dtChannel.Rows[0][0] != DBNull.Value)
                    {
                        return decimal.Parse(dtChannel.Rows[0][0].ToString());
                    }
                }
            }
            catch
            {

            }
            return 0;
        }

        /// <summary>
        /// 获取交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMidJYMoney(string begindate, string endDate, string chnnel)
        {
            decimal MinMoney = GetMinJYMoney(begindate, endDate, chnnel);
            decimal MaxMoney = GetMaxJYMoney(begindate, endDate, chnnel);
            return (MinMoney + MaxMoney) / 2;
        }

        /// <summary>
        /// 交易单笔金额异常监测统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories008(string strType, string LastDate)
        {
            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            begindate = DateTime.Parse(LastDate).AddMonths(-3).ToString("yyyy-MM-dd");
            enddate = DateTime.Parse(LastDate).ToString("yyyy-MM-dd");

            string strChannel = "";
            switch (strType)
            {
                case "0":
                    strChannel = "通信达";
                    break;
                case "1":
                    strChannel = "柜台委托";
                    break;
                case "2":
                    strChannel = "网上交易";
                    break;
                case "3":
                    strChannel = "电话委托";
                    break;
                case "4":
                    strChannel = "程序化交易";
                    break;
                default:
                    break;
            }

            strXAxis = "['最小值','最大值','平均值','中位数']";

            strData = "[";
            strData += GetMinOneJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetMaxOneJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetAvgOneJYMoney(begindate, enddate, strChannel) + ",";
            strData += GetMidOneJYMoney(begindate, enddate, strChannel) + "";
            strData += "]";

            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }

        /// <summary>
        /// 获取单笔最小交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMinOneJYMoney(string begindate, string endDate, string chnnel)
        {
            string strSql = @"select min(TRANSACTIONMENOY)   From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "'";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][0] != DBNull.Value)
                {
                    return decimal.Parse(dtChannel.Rows[0][0].ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 获取单笔最大交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMaxOneJYMoney(string begindate, string endDate, string chnnel)
        {
            string strSql = @"select max(TRANSACTIONMENOY)   From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "'";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][0] != DBNull.Value)
                {
                    return decimal.Parse(dtChannel.Rows[0][0].ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 获取单笔最大交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetAvgOneJYMoney(string begindate, string endDate, string chnnel)
        {
            string strSql = @"select avg(TRANSACTIONMENOY)   From Tbt_ClientTransactionBehavior
            where COUNTMM between  '" + begindate + "' and '" + enddate + "' and TRANSACTIONQD = '" + chnnel + "'";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                if (dtChannel.Rows[0][0] != DBNull.Value)
                {
                    return decimal.Parse(dtChannel.Rows[0][0].ToString());
                }
            }
            return 0;
        }


        /// <summary>
        /// 获取中间值交易金额
        /// </summary>
        /// <param name="chnnel"></param>
        /// <returns></returns>
        private decimal GetMidOneJYMoney(string begindate, string endDate, string chnnel)
        {
            decimal MinMoney = GetMinOneJYMoney(begindate, endDate, chnnel);
            decimal MaxMoney = GetMaxOneJYMoney(begindate, endDate, chnnel);
            return (MinMoney + MaxMoney) / 2;
        }



        /// <summary>
        /// 组织SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        private string SetGuestNOsSql(string begindate2, string endDate2, string strWhere)
        {
            string strSql = "";
            string[] listSplit = strWhere.Split('$');
            string[] strList1 = listSplit[0].Split('#');
            string[] strList2 = listSplit[1].Split('#');
            string[] strList3 = listSplit[2].Split('#');
            int intMin = int.Parse(strList1[0].ToString());
            int intMax = int.Parse(strList1[1].ToString());

            StringBuilder listGuest = new StringBuilder();
            listGuest.Append("1=1 ");
            if (intMin > 0)
            {
                strSql = @" select GUESTNO,count(GUESTNO) from Tbt_ClientTransactionBehavior where COUNTMM 
                between  '" + begindate2 + "' and '" + endDate2 + "'  group by GUESTNO having count(GUESTNO) between " + intMin + " and " + intMax + " ";
                listGuest.Append("and " + GetGUESTNOSByCondition(strSql));
            }

            int minMoney = int.Parse(strList2[0].ToString());
            int maxMoney = int.Parse(strList2[1].ToString());
            if (minMoney > 0)
            {
                strSql = @" select  GUESTNO, sum(TRANSACTIONMENOY)  From Tbt_ClientTransactionBehavior where COUNTMM 
                between  '" + begindate2 + "' and '" + endDate2 + "'  group by GUESTNO having sum(TRANSACTIONMENOY) between " + minMoney + " and " + maxMoney + " ";
                listGuest.Append("and " + GetGUESTNOSByCondition(strSql));

            }
            int minOneMoney = int.Parse(strList3[0].ToString());
            int maxOneMoney = int.Parse(strList3[1].ToString());
            if (minOneMoney > 0)
            {
                strSql = @" select GUESTNO From Tbt_ClientTransactionBehavior where COUNTMM 
                between  '" + begindate2 + "' and '" + endDate2 + "'  and TRANSACTIONMENOY between " + minOneMoney + " and " + maxOneMoney + " ";
                listGuest.Append("and " + GetGUESTNOSByCondition(strSql));
            }
            return listGuest.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        private string GetGUESTNOSByCondition(string strSql)
        {
            string strGuesNo = "";
            DataTable dtChannel = GetAllTradingChannelsTable(strSql);
            if (dtChannel.Rows.Count > 0)
            {
                strGuesNo = "GUESTNO in (";
                for (int iRow = 0; iRow < dtChannel.Rows.Count; iRow++)
                {
                    if (dtChannel.Rows[iRow][0] != DBNull.Value)
                    {
                        strGuesNo += "'" + dtChannel.Rows[iRow][0].ToString() + "'";
                        if (iRow != dtChannel.Rows.Count - 1)
                        {
                            strGuesNo += ",";
                        }
                    }
                }
                strGuesNo += ")";
            }
            else
            {
                return " 1=2";
            }
            return strGuesNo;
        }


        /// <summary>
        /// 异常交易在各渠道客户数统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories009(string endDate, string strWhere)
        {
            string begindate = DateTime.Parse(endDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(endDate).ToString("yyyy-MM-dd");

            string strSqlwhere = SetGuestNOsSql(begindate, endDate, strWhere);

            string strSql = @"select TRANSACTIONQD as JYChnnel, count(distinct GUESTNO) as JYCount   
                From Tbt_ClientTransactionBehavior where  " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "' group by TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);

            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYCount"] != DBNull.Value)
                    {
                        strData += dr["JYCount"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 最近三个月交易渠道交易金额异常监测统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories010(string endDate, string strWhere)
        {
            string begindate = DateTime.Parse(endDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(endDate).ToString("yyyy-MM-dd");

            string strSqlwhere = SetGuestNOsSql(begindate, endDate, strWhere);

            string strSql = @"select TRANSACTIONQD as JYChnnel, count(GUESTNO) as JYCount   From Tbt_ClientTransactionBehavior 
                    where " + strSqlwhere + " and COUNTMM between  '" + begindate + "' and '" + enddate + "' group by TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);

            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            strXAxis = "[";
            strData = "[";
            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYCount"] != DBNull.Value)
                    {
                        strData += dr["JYCount"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }


        /// <summary>
        /// 最近三个月交易渠道交易金额异常监测统计
        /// add by jy
        /// </summary>
        /// <param name="strType"></param>
        /// <returns></returns>
        public List<string> GetJYChnnelCategories011(string endDate, string strWhere)
        {
            string begindate = DateTime.Parse(endDate).AddMonths(-3).ToString("yyyy-MM-dd");
            string enddate = DateTime.Parse(endDate).ToString("yyyy-MM-dd");

            string strSqlwhere = SetGuestNOsSql(begindate, endDate, strWhere);

            string strSql = @"select TRANSACTIONQD as JYChnnel, sum(TRANSACTIONMENOY) as JYCount  From Tbt_ClientTransactionBehavior
                where " + strSqlwhere + "  and COUNTMM between  '" + begindate + "' and '" + enddate + "'  group by  TRANSACTIONQD";

            DataTable dtChannel = GetAllTradingChannelsTable(strSql);

            List<string> lstString = new List<string>();
            string strXAxis = "";
            string strData = "";
            strXAxis = "[";
            strData = "[";

            if (dtChannel.Rows.Count > 0)
            {
                foreach (DataRow dr in dtChannel.Rows)
                {
                    if (dr["JYChnnel"] != DBNull.Value)
                    {
                        strXAxis += "'" + dr["JYChnnel"].ToString() + "'";
                        strXAxis += ",";
                    }

                    if (dr["JYCount"] != DBNull.Value)
                    {
                        strData += dr["JYCount"].ToString();
                        strData += ",";
                    }
                }
                strXAxis = strXAxis.Remove(strXAxis.Length - 1, 1);
                strData = strData.Remove(strData.Length - 1, 1);
            }
            strXAxis += "]";
            strData += "]";
            lstString.Add(strXAxis);
            lstString.Add(strData);
            return lstString;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable GetAllTradingChannelsTable(string strSql)
        {
            ExecuteSql sqldbAccess = new ExecuteSql();
            DataSet ds = sqldbAccess.ExecuteDataset(strSql);
            return ds.Tables[0];
        }



    }
}